﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_Rights_CanAccessByTeam_V2]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_Rights_CanAccessByTeam_V2];
GO
CREATE PROCEDURE [dbo].[sproc_Rights_CanAccessByTeam_V2]
        @ClassID    int,
        @StaffID    int,
        @Act_ID     int,
        @AllowAccess bit output
/*

======================================================================
功能:    得到项目组中@UserName是否能对@ClassID组进行@Act_ID操作
参数:
    @ClassID int    :    组（类）ID
    @StaffID int    :    用户编号
    @Act_ID int     :    权利代号

备注:    继承指是否继承在父组中的权利(在父组中有权利即在此组中有权利)
======================================================================

*/
AS
BEGIN
SET NOCOUNT ON

SET @AllowAccess = 0;

--以下判断是判断用户是否再任何一个能访问此类的父类的组中(能访问父类就能访问子类)

--得到所有父组中拥有此权利的组ID
DECLARE @pAssignedTeam TABLE (TeamID int not null);
INSERT INTO @pAssignedTeam (TeamID)
    SELECT Team_ID
        FROM 
            dbo.UDS_Assign_Rule
        WHERE
            Act_ID = @Act_id
            AND Based_On = 2 /* team */
            AND Team_ID IN (
                SELECT ClassID FROM dbo.fn_get_parent_class(@ClassID)
            );


--返回用户是否在有权利的组中（组成员都具有此权利，不管是组长还是成员）
IF EXISTS (
    SELECT 1
        FROM dbo.UDS_Staff_In_Team A INNER JOIN @pAssignedTeam B ON A.Team_ID = B.TeamID
        WHERE A.Staff_ID = @StaffID
)
BEGIN
    SET @AllowAccess = 1;
    RETURN 0;
END

--得到组角色的权利
IF EXISTS (
    SELECT 1
        FROM
            dbo.UDS_Staff_In_Team A,
            dbo.UDS_Activity B
        WHERE
            A.member_type = B.act_id
            AND B.proc_id = @Act_id
            AND A.Staff_ID = @StaffID
            AND A.team_id in (
                SELECT ClassID FROM dbo.fn_get_parent_class(@ClassID)
            )
)
BEGIN
    SET @AllowAccess = 1;
END

END
GO
